﻿using MX.Core.SQLGeneration.Builders;
using MX.Core.SQLGeneration.Generators;
using System;
using System.Collections.Generic;
using System.Data; 
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using MX.Core.Extensions;
using MX.Core.Caching.Memory;
using System.Data.SqlClient;
using MX.Core.ORM;

namespace Dapper
{
    static public class ExtendsSqlMapper
    {


        /// <summary>
        /// 获取默认一条数据，没有则为NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T SingleOrDefault<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null,
                                            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
        {
            var result = cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
            return result.FirstOrDefault();
        }




        public static PagerResult<T> PageList<T>(this IDbConnection cnn, string sql, int pageIndex, int pageSize, object param = null, IDbTransaction transaction = null,
                                                  bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            string countSql = ParseCountSql(sql);
            return PageList<T>(cnn, sql, countSql, pageIndex, pageSize, param, transaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页取值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static PagerResult<T> PageList<T>(this IDbConnection cnn, string sql, string countSql, int pageIndex, int pageSize, object param = null, IDbTransaction transaction = null,
                                                  bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            string selectSql;

            if (cnn is SqlConnection)
            {
                //默认SQL SERVER
                selectSql = sql.Concats(" OFFSET({0} - 1) * {1} ROWS FETCH NEXT {1} ROWS ONLY;".Formats(pageIndex, pageSize));
            }
            else
            {
                //MYSQL
                selectSql = sql.Concats(" limit {0},{1}".Formats((pageIndex - 1) * pageSize, pageSize));
            }
            var countResult = cnn.ExecuteScalar<long>(countSql, param, transaction, commandTimeout, commandType);
            var listResult = cnn.Query<T>(selectSql, param, transaction, buffered, commandTimeout, commandType);
            PagerResult<T> result = new PagerResult<T>();
            result.PageCount = (int)Math.Ceiling((double)countResult / (double)pageSize);
            result.PageIndex = pageIndex;
            result.PageSize = pageSize;
            result.RecordCount = countResult;
            result.List = listResult.ToList();
            return result;

        }

        static String ParseCountSql(string querySql)
        {
            return MemoryCaching.Default.GetOrAdd(querySql.MD5(), () =>
            {
                SelectBuilder countBuilder = (SelectBuilder)new CommandBuilder().GetCommand(querySql);
                var orderbyList = countBuilder.OrderBy.ToList();
                foreach (var orderby in orderbyList)
                {
                    countBuilder.RemoveOrderBy(orderby);
                }
                var projectionList = countBuilder.Projection.ToList();
                foreach (var projection in projectionList)
                {
                    countBuilder.RemoveProjection(projection);
                }
                countBuilder.AddProjection(new Column("Count(1)"));
                Formatter formatter = new Formatter();
                string countSql = formatter.GetCommandText(countBuilder);
                return countSql;
            });
        }


    }
}
